package model;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedList;

import beans.CommentBean;
import beans.EventBean;
import beans.MessageBean;
import beans.UserBean;

public class MessageDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;
	static ResultSet rs2 = null;

	public static ArrayList<MessageBean> recentMessages(UserBean user, boolean showPrivate) {
		Statement stmt = null;    

		String username = user.getEmail();
		String searchQuery = null;

		if (showPrivate) {
			searchQuery = "select email,name,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where showtoreceiver = true and receiver='" + username + "' order by commenttimestamp desc"; }
		else {
			searchQuery = "select email,name,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where isprivate=false and showtoreceiver = true and receiver='" + username + "' order by commenttimestamp desc"; }

		ArrayList<MessageBean> messageList = new ArrayList<MessageBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	

			for (int i=0; i<3; i++) {
				rs.next();
				MessageBean message = new MessageBean();
				message.setSender(rs.getString(1));
				message.setSenderName(rs.getString(2));
				message.setCommentID(rs.getInt(3));
				message.setBody(rs.getString(4));
				message.setPrivate(rs.getBoolean(5));
				message.setStamp(rs.getTimestamp(6));
				message.setReplyTo(rs.getInt(7));
				message.setShowToSender(rs.getBoolean(8));
				message.setShowToReceiver(rs.getBoolean(9));
				messageList.add(message);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
		return messageList;
	}	

	public static ArrayList<MessageBean> getReceivedMessages(UserBean user) {
		Statement stmt = null;    

		String username = user.getEmail();
		String searchQuery = null;

		searchQuery = "select email,name,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where showtosender = true and receiver='" + username + "' and showtoreceiver = true order by commenttimestamp desc";

		ArrayList<MessageBean> messageList = new ArrayList<MessageBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MessageBean message = new MessageBean();
				message.setSender(rs.getString(1));
				message.setSenderName(rs.getString(2));
				message.setCommentID(rs.getInt(3));
				message.setBody(rs.getString(4));
				message.setPrivate(rs.getBoolean(5));
				message.setStamp(rs.getTimestamp(6));
				message.setReplyTo(rs.getInt(7));
				message.setShowToSender(rs.getBoolean(8));
				message.setShowToReceiver(rs.getBoolean(9));
				messageList.add(message);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return messageList;
	}

	public static ArrayList<MessageBean> getSentMessages(UserBean user) {
		Statement stmt = null;    

		String username = user.getEmail();
		String searchQuery = null;

		searchQuery = "select senderemail,sendername,receiver,name as receivername,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from (select email as senderemail,name as sendername,receiver,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where sender='"+ username + "') as t1 join users on receiver=email where showtosender = true order by commenttimestamp desc";

		ArrayList<MessageBean> messageList = new ArrayList<MessageBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MessageBean message = new MessageBean();
				message.setSender(rs.getString(1));
				message.setSenderName(rs.getString(2));
				message.setReceiver(rs.getString(3));
				message.setReceiverName(rs.getString(4));
				message.setCommentID(rs.getInt(5));
				message.setBody(rs.getString(6));
				message.setPrivate(rs.getBoolean(7));
				message.setStamp(rs.getTimestamp(8));
				message.setReplyTo(rs.getInt(9));
				message.setShowToSender(rs.getBoolean(10));
				message.setShowToReceiver(rs.getBoolean(11));
				messageList.add(message);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return messageList;
	}

	public static void sendMessage(MessageBean message) {
		Statement stmt = null;    

		String sender = message.getSender();
		String receiver = message.getReceiver();
		boolean isPrivate = message.isPrivate();
		int replyTo = message.getReplyTo();
		String body = message.getBody();

		String insertMessageQuery = "insert into message (text, commenttimestamp, replyto) values ('" + body + "', CURRENT_TIMESTAMP, " + replyTo + ")";
		String insertSendQuery = "insert into send values ('" + sender + "', '" + receiver + "', (SELECT MAX(id) FROM message), " + isPrivate + ", true, true)";

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(insertMessageQuery);
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(insertSendQuery);
		} 

		catch (Exception ex) {
			System.out.println("FAIL!2: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}

	public static void deleteMessage(MessageBean message, int inbox) {
		Statement stmt = null;    

		int id = message.getCommentID();
		String deleteQuery = null;
		if (inbox == 1) 
			deleteQuery = "update send set showtosender = false where message = " + id;
		else
			deleteQuery = "update send set showtoreceiver = false where message = " + id;

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(deleteQuery);
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}


	public static LinkedList<MessageBean> getConversation(MessageBean message) {
		Statement stmt = null;    
		String initialSearch = "select senderemail,sendername,receiver,name as receivername,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from (select email as senderemail,name as sendername,receiver,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where message="+ message.getCommentID() + ") as t1 join users on receiver=email order by message desc";		

		LinkedList<MessageBean> messageList = new LinkedList<MessageBean>();
		MessageBean messageIn = new MessageBean();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(initialSearch);
			while (rs.next()) {
				messageIn.setSender(rs.getString(1));
				messageIn.setSenderName(rs.getString(2));
				messageIn.setReceiver(rs.getString(3));
				messageIn.setReceiverName(rs.getString(4));
				messageIn.setCommentID(rs.getInt(5));
				messageIn.setBody(rs.getString(6));
				messageIn.setPrivate(rs.getBoolean(7));
				messageIn.setStamp(rs.getTimestamp(8));
				messageIn.setReplyTo(rs.getInt(9));
				messageIn.setShowToSender(rs.getBoolean(10));
				messageIn.setShowToReceiver(rs.getBoolean(11));
				messageList.addFirst(new MessageBean(messageIn.getSender(), messageIn.getSenderName(), messageIn.getReceiver(), messageIn.getReceiverName(), messageIn.getCommentID(), messageIn.isPrivate(), messageIn.getBody(), messageIn.getStamp(), messageIn.getReplyTo(), messageIn.getShowToSender(), messageIn.getShowToReceiver()));
			}

			while (messageIn.getCommentID() != 1) {
				rs = stmt.executeQuery("select senderemail,sendername,receiver,name as receivername,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from (select email as senderemail,name as sendername,receiver,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where message="+ messageIn.getReplyTo() + ") as t1 join users on receiver=email order by message desc");
				while (rs.next()) {
					messageIn.setSender(rs.getString(1));
					messageIn.setSenderName(rs.getString(2));
					messageIn.setReceiver(rs.getString(3));
					messageIn.setReceiverName(rs.getString(4));
					messageIn.setCommentID(rs.getInt(5));
					messageIn.setBody(rs.getString(6));
					messageIn.setPrivate(rs.getBoolean(7));
					messageIn.setStamp(rs.getTimestamp(8));
					messageIn.setReplyTo(rs.getInt(9));
					messageIn.setShowToSender(rs.getBoolean(10));
					messageIn.setShowToReceiver(rs.getBoolean(11));
					if ((messageIn.getCommentID() != 1) && (messageIn.getShowToReceiver() == true) && (messageIn.getShowToSender() == true)) {
						messageList.addFirst(new MessageBean(messageIn.getSender(), messageIn.getSenderName(), messageIn.getReceiver(), messageIn.getReceiverName(), messageIn.getCommentID(), messageIn.isPrivate(), messageIn.getBody(), messageIn.getStamp(), messageIn.getReplyTo(), messageIn.getShowToSender(), messageIn.getShowToReceiver()));
					}
				}
			}
			messageIn = message;
			while (messageIn.getCommentID() != 1) {
				rs = stmt.executeQuery("select senderemail,sendername,receiver,name as receivername,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from (select email as senderemail,name as sendername,receiver,message,text,isprivate,commenttimestamp,replyto,showtosender,showtoreceiver from send join users on sender=email join message on message=message.id where replyto="+ messageIn.getCommentID() + ") as t1 join users on receiver=email order by message desc");
				if(rs.next()) {
					messageIn.setSender(rs.getString(1));
					messageIn.setSenderName(rs.getString(2));
					messageIn.setReceiver(rs.getString(3));
					messageIn.setReceiverName(rs.getString(4));
					messageIn.setCommentID(rs.getInt(5));
					messageIn.setBody(rs.getString(6));
					messageIn.setPrivate(rs.getBoolean(7));
					messageIn.setStamp(rs.getTimestamp(8));
					messageIn.setReplyTo(rs.getInt(9));
					messageIn.setShowToSender(rs.getBoolean(10));
					messageIn.setShowToReceiver(rs.getBoolean(11));
					if (messageIn.getShowToReceiver() == true && messageIn.getShowToSender() == true)
						messageList.addLast(new MessageBean(messageIn.getSender(), messageIn.getSenderName(), messageIn.getReceiver(), messageIn.getReceiverName(), messageIn.getCommentID(), messageIn.isPrivate(), messageIn.getBody(), messageIn.getStamp(), messageIn.getReplyTo(), messageIn.getShowToSender(), messageIn.getShowToReceiver()));
				} 
				else {
					messageIn.setCommentID(1);
				}
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return messageList;		
	}

	public static ArrayList<CommentBean> getEventComments(EventBean event) {
		Statement stmt = null;    

		String host = event.getHost();
		String stamp = event.getStartTimeStamp().toString();
		String searchQuery = "select writer,name,comments,text,commenttimestamp,host,eventtimestamp from writeabout join users on writer=email join comments on comments=comments.id where host='" + host + "' and eventtimestamp='" + stamp + "' order by commenttimestamp desc";

		ArrayList<CommentBean> commentList = new ArrayList<CommentBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	

			while (rs.next()) {
				CommentBean comment = new CommentBean();
				comment.setWriter(rs.getString(1));
				comment.setWriterName(rs.getString(2));
				comment.setCommentId(rs.getInt(3));
				comment.setBody(rs.getString(4));
				comment.setStamp(rs.getTimestamp(5));
				comment.setEventHost(rs.getString(6));
				comment.setEventStamp(rs.getTimestamp(7));
				commentList.add(comment);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
		return commentList;
	}	

	public static void writeComment(CommentBean comment) {
		Statement stmt = null;    

		String writer = comment.getWriter();
		String eventHost = comment.getEventHost();
		Timestamp eventStamp = comment.getEventStamp();
		String body = comment.getBody();

		String insertCommentQuery = "insert into comments (text, commenttimestamp) values ('" + body + "', CURRENT_TIMESTAMP)";
		String insertWriteaboutQuery = "insert into writeabout values ('" + writer + "', '" + eventHost + "', '" + eventStamp + "', (SELECT MAX(id) FROM comments))";

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(insertCommentQuery);
			stmt.executeUpdate(insertWriteaboutQuery);
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
}